package dashboard.service;

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Map;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import dashboard.model.ParamMaster;
import dashboard.utils.*;

/**
 * 
 * @author xu guoneng
 *
 */
public class DatabaseService extends JdbcDaoSupport {
	
	@Value("${jdbc.username}")
	public String DB_USER; 	//"system";
	
	@Value("${jdbc.password}")
	public String DB_PWD ; 	//"123456";
	
	@Value("${jdbc.instance}")
	public String DB_INSTANCE_NAME;  //"dbtune";
	
	protected String SIMPLE_SQL_RESULT_OUT_FILE = "out.html";

	protected String getResultOutputFilePath(){
		return FileUtil.FILE_DIR + SIMPLE_SQL_RESULT_OUT_FILE;
	}
	
	/**
	 * To retrieve the X-value, Y-value and Healthy/Not Healthy/Attention thresholds
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ParamMaster getParamMaster(){
		String sql = "SELECT * FROM DBTUNE_PARAM_MASTER";
		 
		ParamMaster pm = (ParamMaster)getJdbcTemplate().queryForObject(
				sql, new BeanPropertyRowMapper(ParamMaster.class));
		return pm;
	}
	
	/**
	 * To update the X-value, Y-value and Healthy/Not Healthy/Attention thresholds
	 * @param param
	 * @return the number of record updated i.e. 1
	 */
	public int updateParamMaster(ParamMaster param){
		String sql = "update DBTUNE_PARAM_MASTER " +
					 " set X_VAL = ?, Y_VAL = ?, " +
					 " SP_HEALTHY =?, SP_ATTN =?," +
					 " BC_HEALTHY =?, BC_ATTN =?," +
					 " RD_HEALTHY =?, RD_ATTN =?," +
					 " SM_HEALTHY =?, SM_ATTN =?";
	 
		return getJdbcTemplate().update(sql, 
				new Object[] {param.getxVal(), param.getyVal(),
				param.getSpHealthy(), param.getSpAttn(),
				param.getBcHealthy(), param.getBcAttn(),
				param.getRdHealthy(), param.getRdAttn(),
				param.getSmHealthy(), param.getSmAttn()
			});

	}

	public long getDbId() {
		return getJdbcTemplate().queryForLong("select dbid from v$database");
	}

	public long getInstanceId() {
		return getJdbcTemplate()
				.queryForLong(
						"select instance_number from v$instance where instance_name = ?",
						"dbtune");
	}
	
	protected String executeSqlScript(String iFileName){
		String sqlCmd = "sqlplus";
        String arg1   = DB_USER + "/" + DB_PWD + "@" + DB_INSTANCE_NAME; //"system/123456@dbtune"
        String arg2   = "@" + iFileName;
        
        String outputs = "";
        try {
        		FileUtil.deleteFile(getResultOutputFilePath());
        		
	            ProcessBuilder pb = new ProcessBuilder(sqlCmd, arg1, arg2);
	            Map<String, String> env = pb.environment();
	            env.put("VAR1", arg1);
	            env.put("VAR2", arg2);
	            pb.directory(new File(FileUtil.FILE_DIR));
	            pb.redirectErrorStream(true);
	            Process p = pb.start();
	     	            
	            InputStream is = p.getInputStream();
	            BufferedReader br = new BufferedReader(new InputStreamReader(is));

	            String lineRead;
	            while ((lineRead = br.readLine()) != null) {
	              // swallow the line, or print it out - System.out.println(lineRead);
	            }
	            
	            try {
	            	System.out.println("waiting...");
	        	    p.waitFor();

	        	    //missing these was causing the mass amounts of open 'files'
	        	    is.close();
	        	    p.getOutputStream().close();
	        	    p.getErrorStream().close(); 
	        	    
	        	    System.out.println("waiting done...");
	        	 } 
	            catch (Exception ioe) {
	        		 ioe.printStackTrace();
	        	 }
	            
	            outputs = FileUtil.readFile(getResultOutputFilePath()).trim();
        }
        catch (Exception err) {
          err.printStackTrace();
          outputs = "Error: \n" + err.getMessage();
        }

        //System.out.println(outputs);
		return outputs;
	}

	protected void releaseResource(Connection conn, Statement ps, ResultSet rs) {
		try {
			rs.close();
		} catch (Exception ex) {
		}
		try {
			ps.close();
		} catch (Exception ex) {
		}
		try {
			conn.close();
		} catch (Exception ex) {
		}
	}
}
